#!/usr/bin/python2
# coding: utf-8

import psycopg2

def update_grp(grp_db):
	conn = psycopg2.connect(database="ff_schedule", user="postgres", password="postgres")
	cur = conn.cursor()
	cur.execute("DROP TABLE IF EXISTS groups;")
	cur.execute("CREATE TABLE groups (id SERIAL, group_no smallint, subgroup_no smallint, spec_id integer NOT NULL, PRIMARY KEY (id), FOREIGN KEY (spec_id) REFERENCES specs (id));")
	for i in range(len(grp_db)):
		for j in range(len(grp_db[i])):
			tmp_spec = grp_db[i][j][1][1]
			cur.execute("INSERT INTO groups (group_no, subgroup_no, spec_id) VALUES (%s, 1, %s);", (grp_db[i][j][1], tmp_spec))
			if grp_db[i][j][0] == '2':	
				cur.execute("INSERT INTO groups (group_no, subgroup_no, spec_id) VALUES (%s, 2, %s);", (grp_db[i][j][1], tmp_spec))

	conn.commit()
	cur.close()
	conn.close()
	#print(tmp)

def update_schedule(schd_db, dates, grp_db):
	conn = psycopg2.connect(database="ff_schedule", user="postgres", password="postgres")
	cur = conn.cursor()
	cur.execute("DROP TABLE IF EXISTS dates CASCADE;")
	cur.execute("CREATE TABLE dates (id SERIAL, day_date char(12), PRIMARY KEY (id));")
	for i in range(len(dates)):
		cur.execute("INSERT INTO dates (day_date) VALUES (%s);", (dates[i], ))
		
	cur.execute("DROP TABLE IF EXISTS schedule;")
	cur.execute("""CREATE TABLE schedule
	(id SERIAL, day integer NOT NULL, group_id integer, sched text,
	PRIMARY KEY (id), FOREIGN KEY (day) REFERENCES dates (id),
	FOREIGN KEY (group_id) REFERENCES groups (id));""")
	s = 0
	for i in range(len(schd_db)):
		for j in range(len(schd_db[i][0])):
			s += 1
			for k in range(6):
				cur.execute("""INSERT INTO schedule (day, group_id, sched)
				VALUES (%s, %s, %s);""", (k+1, s, schd_db[i][k][j]))
				
	conn.commit()
	cur.close()
	conn.close()

def get_schedule(grp, sub_grp, day=0):
	conn = psycopg2.connect(database="ff_schedule", user="postgres", password="postgres")
	cur = conn.cursor()
	if day != 0:
		cur.execute("""SELECT day_date, sched FROM schedule, groups, dates
		WHERE groups.id = schedule.group_id and dates.id = schedule.day
		and groups.group_no = %s and groups.subgroup_no = %s
		and schedule.day = %s;""", (grp, sub_grp, day))
	else:
		cur.execute("""SELECT day_date, sched FROM schedule, groups, dates
		WHERE groups.id = schedule.group_id and dates.id = schedule.day
		and groups.group_no = %s and groups.subgroup_no = %s
		""", (grp, sub_grp))

	res = '\n'
	for rec in cur:
		res += rec[0] + '\n' + rec[1] + '\n'
	
	conn.commit()
	cur.close()
	conn.close()
	return res
